An index is a list of keys, each of which identifies a unique database record. An index is based on one or more columns of a database table. Indexes are useful because using an index on a database table to find specific records in the table, and to sort records of the table by columns of the table on which index is based, is faster than performing the same actions without using the index.
Often, an index cannot be created on a table. For example, the size of the data stored in the table may be too large, or the data stored in the table may be of an abstract type. In such a case, a functional index may still be used. A functional index is an index that is defined on the result of a function applied to one or more columns of a database table.
The functional index may be used as a primary filter for the evaluation of certain comparative operators contained within a database query. For example, if there exists a functional index “f(col1)” on a column named col 1 of a table, then the functional index may be used to evaluate the predicate “col1=<value>.” The functional index may be used in processing a database query containing the predicate to obtain a set of rows that satisfy the predicate by evaluating the functional index using the predicate (i.e., f(col1)=f(<value>)). While the rows returned by the functional index (“the returned rows”) may contain additional rows that do not satisfy the predicate “col1=<value>,” the returned rows are guaranteed to contain all the rows of the table that do satisfy the predicate. Each of the returned rows may then be examined to determine if, for a particular row, the predicate “col1=<value>” is true. Examining only the returned rows is more efficient than examining each row of a table identified in the database query. Thus, the query may be processed faster and more efficiently using the functional index.
Currently, a functional index may only be used by a database server if the left hand side or right hand side of the condition referenced in the query is the same as the expression used for the functional index. This limitation reduces the utility of functional indexes. For example, an index cannot be created on columns storing string data larger than the maximum index key size, but a functional index may be created on a prefix of the string data stored in the columns. However, the database server will not consider the functional index in creating the execution plan of a database query that only references the string column name without the prefix function. Consequently, an approach to use functional indexes without incurring the problems associated with prior approaches is desirable.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.